﻿using Newtonsoft.Json.Linq;
using System;
using System.Collections.Generic;
using System.Linq;
using T.FAS.Runtime.Base.Entity;
using T.FAS.Runtime.DataModel.Dao;
using T.FAS.Runtime.Metadata.Entity.DataModel;
using DataColumn = T.FAS.Runtime.Metadata.Entity.DataModel.DataColumn;
using Const = T.FAS.Runtime.DataModel.Plugin.MetaManagerConst;
using System.Data;
using System.Data.SqlClient;
using MySql.Data.MySqlClient;
using Npgsql;
using T.FAS.Runtime.Metadata.Entity;
using System.Text;
using NpgsqlTypes;
using System.Globalization;
//using T.SFP.Term.Dao;
//using T.SFP.Term.ClientService;
using T.FAS.Runtime.Base.Common;
using DataTypeUtil = T.FAS.Runtime.Metadata.Entity.DataModel.DataTypeUtil;

namespace T.FAS.Runtime.DataModel.Plugin
{
    public abstract class DataObjectSQLBuilderBase
    {
        #region 属性字段
        public static CultureInfo Culture_en_US = new CultureInfo("en-US");
        public static NumberStyles NumberStyle_Default = NumberStyles.Number;
        public const string zh_CN = "zh-CN";
        public abstract IList<IMetaDataContent> Contents { get; set; }
        protected virtual DatabaseType DatabaseType { get; }
        /// <summary>
        /// PG数据库特殊处理，是否需要纯小写
        /// </summary>
        protected virtual bool IsNeedLowerCase => false;
        private readonly DataObjectLogic dataObjectLogic = new DataObjectLogic();
        private IList<DataObject> contents
        {
            get
            {
                return Contents?.Select(o => (DataObject)o).ToList();
            }
        }
        #endregion 

        /// <summary>
        /// 比较复杂 需要按db类型实现
        /// </summary>
        /// <param name="dataObject"></param>
        /// <param name="columns"></param>
        /// <returns></returns>
        protected abstract List<SQLItem> GetColumnModifySQL(DataObject dataObject, List<ModifyColumnInfo> columns);

        public List<SQLItem> BuildBusinessSQL(object entity)
        {
            if (!(entity is DataObject dataObject) || dataObject.Columns == null || dataObject.Columns.Count == 0)
                throw new FasException(DataModelErrorCode.ParamError, "生成DataObject元数据SQL失败！检查检查参数");
            DataObject oldObject = dataObjectLogic.GetDataObjectByID(dataObject.ID);
            var resultSQL = GetCreateTableSQL(dataObject);
            //当前环境存在DO时，追加生成列的DDL语句
            if (oldObject != null)
            {
                var ddlSQLList = GetColumnDDL(oldObject, dataObject);
                resultSQL.AddRange(ddlSQLList);
            }
            return resultSQL;
        }

        /// <summary>
        /// 生成静态数据对象(SDO)数据初始化的DML
        /// SDO静态数据量小，数据预置采用先删后加模式
        /// </summary>
        /// <param name="entity">DO实体</param>
        /// <returns>可执行的SQL集合</returns>
        public List<SQLItem> BuildPresetDataSQL(object entity)
        {
            if (!(entity is DataObject dataobject) || dataobject.Columns == null || dataobject.Columns.Count == 0)
                throw new FasException(DataModelErrorCode.ParamError, "生成DataObject元数据SQL失败！检查检查参数");
            List<SQLItem> sqls = new List<SQLItem>();

            sqls.AddRange(GeneratePresetDataDeleteSQL(dataobject));
            sqls.AddRange(GeneratePresetDataInsertSQL(dataobject));
            return sqls;
        }

        #region SDO&国际化
        /// <summary>
        /// 获取主键列值
        /// </summary>
        /// <param name="presetDataRecord"></param>
        /// <param name="pkCol"></param>
        /// <returns></returns>
        protected object GetPkVal(JObject presetDataRecord, DataColumn pkCol)
        {
            string pkVal = null;
            if (!presetDataRecord.TryGetValue(pkCol.ColumnName, StringComparison.OrdinalIgnoreCase, out JToken valToken))
            {
                return null;
            }
            else
            {
                if (valToken is JValue jVal && (jVal.Type == JTokenType.String || jVal.Type == JTokenType.Integer))
                {
                    pkVal = jVal.Value?.ToString();
                }
            }
            return pkVal;
        }

        /// <summary>
        /// 获取SDO预支数据词条
        /// </summary>
        /// <param name="do"></param>
        /// <param name="dataColumn"></param>
        /// <param name="pkVal"></param>
        /// <returns></returns>
        protected string GetTermCode(DataObject @do, DataColumn dataColumn, object pkVal)
        {
            var topPath = @do.GetTopRefSchemaExpr();
            var expr = $"{topPath}.PresetData._${pkVal}.{dataColumn.ColumnName}";
            return expr;
        }

        /// <summary>
        /// 获取翻译后的词条
        /// </summary>
        /// <param name="do"></param>
        /// <returns></returns>
        protected Dictionary<string, List<Dictionary<string, string>>> GetTranslatedTerms(DataObject @do)
        {
            /*
             * {
	                "en_US": [
		                {
			                "Code": "Schema.xxx.xxx",
			                "TranslateText": "xxxxx"
		                }
	                ]
                }
             */
            //Dictionary<string, List<Dictionary<string, string>>> dict = new Term.Service.TermDefinitionManager().GetMetaDataTerms(@do.MetaDataID, @do.ProjectBranchID);
            //return dict;
            return @do.TranslatedTerms ?? new Dictionary<string, List<Dictionary<string, string>>>();
        }

        /// <summary>
        /// 获取翻译后文本
        /// </summary>
        /// <param name="translatedTerms"></param>
        /// <param name="termCode"></param>
        /// <param name="i18NCode"></param>
        /// <returns></returns>
        protected string GetTranslatedText(Dictionary<string, List<Dictionary<string, string>>> translatedTerms, string termCode, string i18NCode)
        {
            //生成词条code
            string translatedText = null;
            // translatedTerms key:zh-CN  value:{termCode: translatedText}
            if (translatedTerms != null && translatedTerms.ContainsKey(i18NCode))
            {
                translatedText = translatedTerms[i18NCode].FirstOrDefault(dic => dic["Code"] == termCode)?["TranslateText"];
                if (string.IsNullOrEmpty(translatedText))
                {
                    /* Todo
                    translatedText = TermClientService.GetInstance().GetTranslateText(termCode, i18NCode);
                    */
                }
            }
            return translatedText;
        }

        /// <summary>
        /// 获取SDO预支数据
        /// </summary>
        /// <param name="item"></param>
        /// <param name="col"></param>
        /// <param name="contents"></param>
        /// <returns></returns>
        protected string GetSDOPresetDataValue(JToken item, DataColumn col, IList<DataObject> contents)
        {
            string val = item.Value<string>(col.ColumnName);
            if (!string.IsNullOrEmpty(col.ApplicationDataType))
            {
                //特殊处理Boolean类型，存储1,0
                if (DataTypeTool.MappingDataColumnDataTypeToCNOElementDataType(col, contents) == DataType.Boolean)
                {
                    if (val.ToLower() == "true")
                        val = "1";
                    else if (val.ToLower() == "false")
                        val = "0";
                }
            }

            return val;
        }
        #endregion

        #region SQL模板拼接

        /// <summary>
        /// 获取SQL模板
        /// </summary>
        /// <param name="dataObject"></param>
        /// <returns></returns>
        protected SQLItem GetTemplate(DataObject dataObject)
        {
            DatabaseType dbType = DataTypeUtil.ConvertToDatabaseType(dataObject.Database.DbType);
            SQLItem template = new SQLItem
            {
                MetaDataID = dataObject?.ID,
                MetaDataCode = dataObject?.Code,
                MetaDataType = DataModelConst.DOMetaType,
                SQLType = SQLType.DDL,
                ConfigKey = dataObject.Database.DatabaseConfigs.FirstOrDefault(cfg => cfg.IsDefault)?.ConfigKey,
                DatabaseType = dbType,
                IsCanRepeat = false,
            };
            return template;
        }

        protected virtual List<SQLItem> GetCreateTableSQL(DataObject dataObject)
        {
            /*******************构造目标数据库表DDL*******************/
            var createTablePKDef = GetSQLTemplate(SQLOpType.CREATE_PKDef);
            var createTableTempalte = GetSQLTemplate(SQLOpType.CREATE);
            #region 创建Create Table语句
            var template = GetTemplate(dataObject);
            //判断是否需要创建表
            SQLItem createTableItem = template.Clone();

            //创建列片段
            StringBuilder sbColumn = new StringBuilder();
            List<DataColumn> columnWithSort = dataObject.Columns.OrderBy(col => col.ColumnOrder).ToList();
            foreach (DataColumn column in columnWithSort)  //增加排序，确保物理表的列顺序跟DO一致
            {
                List<string> createColumnSQL = GetCreateColumnSQLs(template, column, dataObject);
                if (createColumnSQL != null && createColumnSQL.Count > 0)
                {
                    foreach (var sql in createColumnSQL)
                    {
                        sbColumn.Append(sql);
                    }
                }
            }

            //创建主键PK代码片段
            DataColumn pkcol = dataObject.Columns.Find(col => col.IsPkColumn);
            string pkSection = createTablePKDef.Format(
                    new Dictionary<string, object>
                    {
                        {Const.pk_name, ProcessCaseString(dataObject.LogicTableName + "_" + pkcol.ColumnName) },
                        {Const.pk_column_name, ProcessCaseString(pkcol.ColumnName) },
                    });

            //创建CreateTable建表语句
            createTableItem.SQL = createTableTempalte.Format(
                    new Dictionary<string, object>
                    {
                        {Const.db_name,  ProcessCaseString(dataObject.Database.Code) },
                        {Const.table_name, ProcessCaseString(dataObject.LogicTableName) },
                        {"column_def", sbColumn.ToString()},
                        {"pk_constraint_def", pkSection},
                    });

            return new List<SQLItem> { createTableItem };
            #endregion
        }

        /// <summary>
        /// 比对DO差异，生成DDL语句
        /// </summary>
        /// <param name="oldObject"></param>
        /// <param name="newObject"></param>
        /// <returns></returns>
        protected virtual List<SQLItem> GetColumnDDL(DataObject oldObject, DataObject newObject)
        {
            #region 创建Alter ADD|DROP|ALTER Column语句
            List<SQLItem> columnDDLSQLs = new List<SQLItem>();
            var result = DataObjectChangeChecker.Compare(oldObject, newObject, CheckEnvironment.Release);

            var addColumns = GetColumnADDSQL(newObject, result.DataColumnCompareResult.NeedAddColumns);
            var modifyColumns = GetColumnModifySQL(newObject, result.DataColumnCompareResult.NeedModifyColumns);
            //var dropColumns = GetColumnDropSQL(oldObject, result.DataColumnCompareResult.NeedDropColumns);

            columnDDLSQLs.AddRange(addColumns);
            columnDDLSQLs.AddRange(modifyColumns);
            //columnDDLSQLs.AddRange(dropColumns);
            #endregion

            //如果辅助语言设置变了，比如 _Lang1 变为 _Language1，需要重新生成ddl
            if (result.ParalanguageCompareResult.IsChanged)
            {
                var interColAddSQLs = GetInternalParalanguageChangeSQL(newObject, result);
                columnDDLSQLs.AddRange(interColAddSQLs);
            }

            return columnDDLSQLs;
        }

        /// <summary>
        /// 获取新增列SQL
        /// </summary>
        /// <param name="dataObject"></param>
        /// <param name="databaseDataType"></param>
        /// <param name="column"></param>
        /// <returns></returns>
        protected virtual List<string> GetColumnAddSQLs(DataObject dataObject, string databaseDataType, DataColumn column)
        {
            List<string> sqls = new List<string>();
            var columnAddTemplate = GetSQLTemplate(SQLOpType.ColumnAdd);

            sqls.Add(columnAddTemplate.Format(
            new Dictionary<string, object>
            {
                {Const.table_name, ProcessCaseString(dataObject.LogicTableName) },
                {Const.column_name, ProcessCaseString(column.ColumnName) },
                {Const.data_type, databaseDataType},
                {Const.nullable, column.IsNullable ? " NULL ": " NOT NULL " },
                {Const.@default, DealWithDefault(column, true) },
                {Const.comment, DealWithComment(column, true) },
            }));

            var internalSqls = GetInternalColumnAddSQLs(dataObject, databaseDataType, column);
            if (internalSqls.Count > 0)
                sqls.AddRange(internalSqls);

            var maskingSqls = GetMaskingColumnAddSQLs(dataObject, databaseDataType, column);
            if (maskingSqls.Count > 0)
                sqls.AddRange(maskingSqls);
            return sqls;
        }

        /// <summary>
        /// 获取新增列SQL，特殊处理国际化列
        /// </summary>
        /// <param name="dataObject"></param>
        /// <param name="databaseDataType"></param>
        /// <param name="column"></param>
        /// <returns></returns>
        protected virtual List<string> GetMaskingColumnAddSQLs(DataObject dataObject, string databaseDataType, DataColumn column)
        {
            List<string> sqls = new List<string>();
            var columnAddTemplate = GetSQLTemplate(SQLOpType.ColumnAdd);
            bool isStringType = string.Equals(column.ApplicationDataType, DataType.String.ToString(), StringComparison.OrdinalIgnoreCase);

            // 不支持掩码的列类型： 系统字段、主键、外键
            //如果没有开启低代码掩码存储，不生成sql
            if (column.SysColumnFlag == SysColumnFlag.None && !column.IsPkColumn && !column.IsFkColumn && column.MaskingRule.IsEnable && ConfigUtil.Instance.EnableFASMaskStorage)
            {
                var maskColName = DataColumn.GetMaskColumnName(dataObject, column.ColumnName);
                var maskDataType = isStringType ? databaseDataType : "varchar(50)";    //除string类型外，统一转成长度50的字符类型
                var maskColumnSQL = columnAddTemplate.Format(
                    new Dictionary<string, object>
                    {
                        {Const.table_name, ProcessCaseString(dataObject.LogicTableName) },
                        {Const.column_name, ProcessCaseString(maskColName) },
                        {Const.data_type, maskDataType},
                        {Const.nullable, " NULL "}, //国际化列必须可空
                        {Const.@default, "" },
                        {Const.comment, DealWithComment(column, true) },
                    });
                sqls.Add(maskColumnSQL);
            }
            return sqls;
        }

        /// <summary>
        /// 获取新增列SQL，特殊处理国际化列
        /// </summary>
        /// <param name="dataObject"></param>
        /// <param name="databaseDataType"></param>
        /// <param name="column"></param>
        /// <returns></returns>
        protected virtual List<string> GetInternalColumnAddSQLs(DataObject dataObject, string databaseDataType, DataColumn column)
        {
            List<string> sqls = new List<string>();
            var columnAddTemplate = GetSQLTemplate(SQLOpType.ColumnAdd);

            bool isStringType = string.Equals(column.ApplicationDataType, DataType.String.ToString(), StringComparison.OrdinalIgnoreCase);
            if (isStringType && column.IsInternational)
            {
                foreach (var langKey in InternationalDataTools.DefaultParalanguage)
                {
                    var i18NColumnName = DataColumn.GetInternationalColumnName(dataObject, column.ColumnName, langKey);
                    var internationalColumnSQL = columnAddTemplate.Format(
                        new Dictionary<string, object>
                        {
                            {Const.table_name, ProcessCaseString(dataObject.LogicTableName) },
                            {Const.column_name, ProcessCaseString(i18NColumnName) },
                            {Const.data_type, databaseDataType},
                            {Const.nullable, " NULL "}, //国际化列必须可空
                            {Const.@default, DealWithDefault(column, true) },
                            {Const.comment, DealWithComment(column, true) },
                        });
                    sqls.Add(internationalColumnSQL);
                }
            }
            return sqls;
        }

        /// <summary>
        /// 生成辅助语言设置变更时的SQL
        /// </summary>
        /// <param name="newObject"></param>
        /// <param name="result"></param>
        /// <returns></returns>
        private List<SQLItem> GetInternalParalanguageChangeSQL(DataObject newObject, DataObjectCompareResult result)
        {
            List<SQLItem> interColAddSQLs = new List<SQLItem>();
            if (result.ParalanguageCompareResult.ChangedParalanguage != null && result.ParalanguageCompareResult.ChangedParalanguage.Count > 0)
            {
                var columnAddTemplate = GetSQLTemplate(SQLOpType.ColumnAdd);
                var template = GetTemplate(newObject);
                var interColumns = newObject.Columns.Where(col => col.IsInternational);
                foreach (var column in interColumns)
                {
                    var databaseDataType = DataTypeTool.GetMappingDataType(template.DatabaseType, column, contents);
                    foreach (var paraLan in result.ParalanguageCompareResult.ChangedParalanguage)
                    {
                        SQLItem interColAddSQL = template.Clone();
                        var i18NColumnName = DataColumn.GetInternationalColumnName(newObject, column.ColumnName, paraLan);
                        interColAddSQL.SQL = columnAddTemplate.Format(
                        new Dictionary<string, object>
                        {
                            {Const.table_name, ProcessCaseString(newObject.LogicTableName) },
                            {Const.column_name, ProcessCaseString(i18NColumnName) },
                            {Const.data_type, databaseDataType},
                            {Const.nullable, " NULL "}, //国际化列必须可空
                            {Const.@default, DealWithDefault(column) },
                            {Const.comment, DealWithComment(column, true) },
                        });
                        interColAddSQLs.Add(interColAddSQL);
                    }
                }
            }
            return interColAddSQLs;
        }

        /// <summary>
        /// 生成创建表时，列定义的sql，特殊处理国际化列
        /// </summary>
        /// <param name="template"></param>
        /// <param name="column"></param>
        /// <returns></returns>
        protected virtual List<string> GetCreateColumnSQLs(SQLItem template, DataColumn column, DataObject dataObject)
        {
            List<string> sqls = new List<string>();
            var createTableColumnDef = GetSQLTemplate(SQLOpType.CREATE_ColDef);
            //外键类型可能会查询DB，会比较慢
            string datatype = DataTypeTool.GetMappingDataType(template.DatabaseType, column, contents);
            string createColumnSQL = createTableColumnDef.Format(
                    new Dictionary<string, object>
                    {
                        {Const.column_name, ProcessCaseString(column.ColumnName) },
                        {Const.data_type, datatype },
                        {Const.nullable, column.IsNullable ? " NULL ": " NOT NULL " },
                        {Const.@default, DealWithDefault(column) },
                        {Const.comment, DealWithComment(column) },
                    });
            sqls.Add(createColumnSQL);
            bool isStringType = string.Equals(column.ApplicationDataType, DataType.String.ToString(), StringComparison.OrdinalIgnoreCase);
            if (isStringType && column.IsInternational)
            {
                foreach (string langKey in InternationalDataTools.DefaultParalanguage)
                {
                    string internationalColumnSQL = createTableColumnDef.Format(
                        new Dictionary<string, object>
                        {
                            {Const.column_name, ProcessCaseString(DataColumn.GetInternationalColumnName(dataObject, column.ColumnName, langKey)) },
                            {Const.data_type, datatype },
                            {Const.nullable, " NULL "}, //国际化列必须可空
                            {Const.@default, ""},       //国际化列不适用默认值
                            {Const.comment, DealWithComment(column) },  //必须加入默认值，否则MySQL的国际化列发布报错
                        });
                    sqls.Add(internationalColumnSQL);
                }
            }

            //建表语句生成掩码字段sql
            if (column.SysColumnFlag == SysColumnFlag.None && !column.IsPkColumn && !column.IsFkColumn && column.MaskingRule.IsEnable && ConfigUtil.Instance.EnableFASMaskStorage)
            {
                var maskColName = DataColumn.GetMaskColumnName(dataObject, column.ColumnName);
                var maskDataType = isStringType ? datatype : "varchar(50)";    //除string类型外，统一转成长度50的字符类型
                var maskColumnSQL = createTableColumnDef.Format(
                    new Dictionary<string, object>
                    {
                        {Const.column_name, ProcessCaseString(maskColName) },
                        {Const.data_type, maskDataType},
                        {Const.nullable, " NULL "}, //必须可空
                        {Const.@default, "" },      //不适用默认值
                        {Const.comment, DealWithComment(column) },  //必须加入默认值，否则MySQL的国际化列发布报错
                    });
                sqls.Add(maskColumnSQL);
            }

            return sqls;
        }

        /// <summary>
        /// 处理默认值, 此处不做类型=>默认值的验证，默认类型跟默认值是类型匹配的，区别在于DEFAULT的写法
        /// </summary>
        /// <param name="column"></param>
        /// <param name="isEscape"></param>
        /// <returns></returns>
        protected virtual string DealWithDefault(DataColumn column, bool isEscape = false)
        {
            string dataType = column.DataType, defaultValue = column.DefaultValue;
            if (string.IsNullOrEmpty(column.DefaultValue?.Trim()))
                return string.Empty;
            //if(defaultValue.ToLower() == "null") //默认值设为NULL无意义 暂时不处理
            var functionList = GetSQLSets(SQLSetType.FunctionList);
            var valueType = GetSQLSets(SQLSetType.ValueType);
            if (defaultValue == "''")
                return isEscape ? $" DEFAULT \\'\\' " : $" DEFAULT '' ";
            if (functionList.Contains(defaultValue.ToLower()))
            {
                return $" DEFAULT {defaultValue} ";
            }
            if (valueType.Contains(dataType.ToLower()))
            {
                return $" DEFAULT {defaultValue} ";
            }
            defaultValue = defaultValue.Replace("(", "").Replace(")", "").Replace("'", ""); //默认作为字符串处理，替换掉特殊字符
            return isEscape ? $" DEFAULT \\'{defaultValue}\\' " : $" DEFAULT '{defaultValue}' ";
        }

        /// <summary>
        /// 处理注释
        /// </summary>
        /// <param name="column"></param>
        /// <param name="isEscape">是否需要转义，在存储过程中使用需要进行转义</param>
        /// <returns></returns>
        protected virtual string DealWithComment(DataColumn column, bool isEscape = false)
        {
            if (isEscape)
                return $" Comment \\'{column.DisplayName}\\' ";
            else
                return $" Comment '{column.DisplayName}' ";
        }

        /// <summary>
        /// 生成SDO预置数据的delete SQL
        /// </summary>
        /// <returns></returns>
        protected List<SQLItem> GeneratePresetDataDeleteSQL(DataObject dataObject)
        {
            string deleteSqlTemplate = GetSQLTemplate(SQLOpType.DELETE);
            string sql = deleteSqlTemplate.Format(new Dictionary<string, object> {
                { Const.db_name, ProcessCaseString(dataObject.Database.Code) },
                { Const.table_name , ProcessCaseString(dataObject.LogicTableName)},
                { "condition" , " WHERE 1=1"},  //联调环境静态数据预置先删后加
            });
            var sqlItem = GetTemplate(dataObject);
            sqlItem.SQL = sql;
            sqlItem.SQLType = SQLType.DML;
            sqlItem.CRUDType = CRUDType.DELETE;
            return new List<SQLItem> { sqlItem };
        }

        /// <summary>
        /// 生成SDO预置数据的Insert SQL
        /// </summary>
        /// <returns></returns>
        protected List<SQLItem> GeneratePresetDataInsertSQL(DataObject dataObject)
        {
            string insertSqlTemplate = GetSQLTemplate(SQLOpType.INSERT);
            if (!dataObject.IsStatic || string.IsNullOrEmpty(dataObject.PresetData?.Content))
                return new List<SQLItem>();
            List<string> columnNames = new List<string>(); //@do.Columns.OrderBy(col => col.ColumnOrder).Select(col => $"`{col.ColumnName}`");
            List<string> columnValues = new List<string>(); //@do.Columns.OrderBy(col => col.ColumnOrder).Select(col => "@" + col.ColumnName);

            foreach (var col in dataObject.Columns.OrderBy(col => col.ColumnOrder))
            {
                columnNames.Add(ProcessCaseString(WrapStringBySystemIdentifier(col.ColumnName)));
                columnValues.Add("@" + col.ColumnName);
                if (col.IsInternational)
                {
                    foreach (var langKey in InternationalDataTools.DefaultParalanguage)
                    {
                        var i18NColName = DataColumn.GetInternationalColumnName(dataObject, col.ColumnName, langKey);
                        columnNames.Add(ProcessCaseString(WrapStringBySystemIdentifier(i18NColName)));
                        columnValues.Add("@" + i18NColName);
                    }
                }
            }

            string sql = insertSqlTemplate.Format(new Dictionary<string, object>
            {
                { Const.db_name,        ProcessCaseString(dataObject.Database.Code) },
                { Const.table_name ,    ProcessCaseString(dataObject.LogicTableName)},
                { "column_names",       string.Join(",", columnNames) },
                { "column_values",      string.Join(",", columnValues) }
            });

            List<SQLItem> sqls = new List<SQLItem>();
            //静态SDO的预置数据是Json格式
            JArray arr = JArray.Parse(dataObject.PresetData.Content);
            DataColumn pkColumn = dataObject.Columns.FirstOrDefault(c => c.IsPkColumn);
            var translatedTerms = GetTranslatedTerms(dataObject);
            if (!translatedTerms.ContainsKey(zh_CN))
            {
                translatedTerms[zh_CN] = new List<Dictionary<string, string>>();
            }
            var notMappingLangs = InternationalDataTools.DefaultParalanguage.Except(InternationalDataTools.LanguageMapping.Keys);
            foreach (JToken item in arr)
            {
                var sqlItem = GetTemplate(dataObject);
                sqlItem.SQL = sql;
                sqlItem.SQLType = SQLType.DML;
                sqlItem.CRUDType = CRUDType.CREATE;
                IDictionary<string, JToken> objDict = (JObject)item;
                object pkVal = GetPkVal((JObject)item, pkColumn);
                foreach (var col in dataObject.Columns)
                {
                    //如果不是国际化字段，走原有的逻辑
                    if (!col.IsInternational || pkVal == null)
                    {
                        if (objDict.Keys.Contains(col.ColumnName))
                        {
                            string val = GetSDOPresetDataValue(item, col, contents);
                            IDbDataParameter sqlParameter = CreateParameter(col, val);
                            sqlItem.Parameters.Add(sqlParameter);
                        }
                        else
                        {
                            IDbDataParameter sqlParameter = CreateParameter(col, DBNull.Value);
                            sqlItem.Parameters.Add(sqlParameter);
                        }
                    }
                    //国际化列走单独逻辑
                    else
                    {
                        var termCode = GetTermCode(dataObject, col, pkVal);
                        string presetColumnValue = GetSDOPresetDataValue(item, col, contents);
                        //填充中文包
                        translatedTerms[zh_CN].Add(new Dictionary<string, string> { { "Code", termCode }, { "TranslateText", presetColumnValue } });

                        //如果当前环境支持国际化，则RTPSupportLanguage包含主语言+辅助语言信息
                        if (InternationalDataTools.RTPSupportLanguage != null && InternationalDataTools.RTPSupportLanguage.Count > 0)
                        {
                            foreach (var language in InternationalDataTools.RTPSupportLanguage)
                            {
                                object insertVal = GetTranslatedText(translatedTerms, termCode, language.ID);
                                if (insertVal == null)
                                    insertVal = DBNull.Value;
                                string parmeterName = col.ColumnName;
                                //如果为辅助语言，则拼接Lang后缀
                                if (!language.IsMainLanguage)
                                {
                                    parmeterName = DataColumn.GetInternationalColumnName(dataObject, col.ColumnName, language.RepositoryCode);
                                }
                                IDbDataParameter sqlParameter = CreateParameter(col, insertVal, parmeterName);
                                sqlItem.Parameters.Add(sqlParameter);
                            }
                        }
                        else
                        {
                            IDbDataParameter sqlParameter = CreateParameter(col, presetColumnValue);
                            sqlItem.Parameters.Add(sqlParameter);
                        }
                        if (notMappingLangs != null)
                        {
                            foreach (var lang in notMappingLangs)
                            {
                                var i18NColName = DataColumn.GetInternationalColumnName(dataObject, col.ColumnName, lang);
                                IDbDataParameter sqlParameter = CreateParameter(col, DBNull.Value, i18NColName);
                                sqlItem.Parameters.Add(sqlParameter);
                            }
                        }
                    }
                }
                sqls.Add(sqlItem);
            }

            return sqls;
        }

        /// <summary>
        /// 生成Column ADD语句
        /// </summary>
        /// <param name="dataObject">DO实体</param>
        /// <param name="columns">DO列实体集合</param>
        /// <returns>可执行的SQL集合</returns>
        protected List<SQLItem> GetColumnADDSQL(DataObject dataObject, List<DataColumn> columns)
        {
            var template = GetTemplate(dataObject);
            //新增column SQL Item
            List<SQLItem> addColumns = new List<SQLItem>();
            foreach (var column in columns)
            {
                var databaseDataType = DataTypeTool.GetMappingDataType(template.DatabaseType, column, contents);
                var addColSQLs = GetColumnAddSQLs(dataObject, databaseDataType, column);
                if (addColSQLs != null && addColSQLs.Count > 0)
                {
                    foreach (var addSQL in addColSQLs)
                    {
                        SQLItem item = template.Clone();
                        item.SQL = addSQL;
                        addColumns.Add(item);
                    }
                }
            }
            return addColumns;
        }

        #endregion

        #region SQL相关方法，后续通过工厂模式搞
        /// <summary>
        /// 创建指定数据库
        /// </summary>
        /// <param name="columnName"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        protected IDbDataParameter CreateParameter(DataColumn dataColumn, object value, string specialName = null)
        {
            string columnName = string.IsNullOrEmpty(specialName) ? dataColumn.ColumnName : specialName;
            switch (DatabaseType)
            {
                case DatabaseType.SQLServer:
                    return new SqlParameter(columnName, value);
                case DatabaseType.MySQL:
                    return new MySqlParameter(columnName, value);
                case DatabaseType.PostgreSQL:
                    object val = value;
                    DataType type = DataTypeTool.MappingDataColumnDataTypeToCNOElementDataType(dataColumn);
                    IValidAndConvertToObject converter = DataTypeToolFactory.ValidAndConvertToObject(type);
                    if (converter != null)
                    {
                        val = converter.ValidAndConvertToObject(value, dataColumn.IsNullable);
                        return GetPGParameters(type, columnName, val);
                    }
                    else
                    {
                        value = ConvertTo(type, value);
                        return new NpgsqlParameter(columnName, value);
                    }
                case DatabaseType.ES:
                case DatabaseType.HBase:
                case DatabaseType.TiDB:
                case DatabaseType.StarRocks:
                case DatabaseType.Druid:
                case DatabaseType.Oracle:
                default:
                    throw new Exception($"不支持的数据库参数类型:{DatabaseType}");
            }
        }

        private object ConvertTo(DataType dataType, object inputVal)
        {
            switch (dataType)
            {
                case DataType.Boolean:
                    if (inputVal is bool boolVal)
                        return boolVal;
                    else if (inputVal is int intBVal)
                    {
                        if (intBVal == 0)
                            return false;
                        else if (intBVal == 1)
                            return true;
                    }
                    else
                    {
                        var stringVal = inputVal.ToString();
                        if (bool.TryParse(stringVal, out bool boolResult))
                        {
                            return boolResult;
                        }
                        else
                        {
                            if (stringVal == "0")
                                return false;
                            else if (stringVal == "1")
                                return true;
                        }
                    }
                    break;
                case DataType.Int:
                    if (inputVal is int intVal)
                    {
                        return intVal;
                    }
                    else
                    {
                        var stringVal = inputVal.ToString();
                        if (int.TryParse(stringVal, out int intVal2))
                        {
                            return intVal2;
                        }
                    }
                    break;
                case DataType.Long:
                    if (inputVal is long longVal)
                    {
                        return longVal;
                    }
                    else
                    {
                        var stringVal = inputVal.ToString();
                        if (long.TryParse(stringVal, out long longVal2))
                        {
                            return longVal2;
                        }
                    }
                    break;
                case DataType.Decimal:
                    if (inputVal is decimal decimalVal)
                    {
                        return decimalVal;
                    }
                    else
                    {
                        var stringVal = inputVal.ToString();
                        if (decimal.TryParse(stringVal, NumberStyle_Default, Culture_en_US, out decimal decimalVal2))
                        {
                            return decimalVal2;
                        }
                    }
                    break;
                case DataType.Timestamp:
                case DataType.Date:
                case DataType.DateTime:
                    if (inputVal is DateTime DateTimeVal)
                    {
                        return DateTimeVal;
                    }
                    else
                    {
                        var val = inputVal.ToString();  //兼容Newtonsoft.Json.Linq.JValue类型
                        if (DateTime.TryParse(val, out DateTime DateTimeVal2))
                        {
                            return DateTimeVal2;
                        }
                    }
                    break;
                case DataType.Time:
                    if (inputVal is TimeSpan TimeVal)
                    {
                        return TimeVal;
                    }
                    else
                    {
                        var stringVal = inputVal.ToString();
                        if (DateTime.TryParse(stringVal, out DateTime dt))
                        {
                            return dt.TimeOfDay;
                        }
                        else if (TimeSpan.TryParse(stringVal, out TimeSpan TimeVal2))
                        {
                            return TimeVal2;
                        }
                    }
                    break;
                case DataType.String:
                    return inputVal?.ToString();
                case DataType.Binary:
                case DataType.Record:
                case DataType.List:
                default:
                    return inputVal;
            }
            return inputVal;
        }

        /// <summary>
        /// 使用特定的系统标识府包裹
        /// </summary>
        /// <param name="content"></param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        protected string WrapStringBySystemIdentifier(string content)
        {
            switch (DatabaseType)
            {
                case DatabaseType.SQLServer:
                    return $"[{content}]";
                case DatabaseType.MySQL:
                    return $"`{content}`";
                case DatabaseType.PostgreSQL:
                    return $"\"{content}\"";
                case DatabaseType.ES:
                case DatabaseType.HBase:
                case DatabaseType.TiDB:
                case DatabaseType.StarRocks:
                case DatabaseType.Druid:
                case DatabaseType.Oracle:
                default:
                    throw new Exception($"不支持的数据库参数类型:{DatabaseType}");
            }
        }

        /// <summary>
        /// 通过数据库类型获取不同的SQL片段
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        protected string GetSQLTemplate(SQLOpType type)
        {
            switch (DatabaseType)
            {
                case DatabaseType.SQLServer:
                    if (type == SQLOpType.INSERT)
                        return Const.MSSQL.INSERT;
                    else if (type == SQLOpType.DELETE)
                        return Const.MSSQL.DELETE;
                    else if (type == SQLOpType.CREATE)
                        return Const.MSSQL.CreateTable;
                    else if (type == SQLOpType.CREATE_PKDef)
                        return Const.MSSQL.CreateTablePKDef;
                    else if (type == SQLOpType.CREATE_ColDef)
                        return Const.MSSQL.CreateTableColumnDef;
                    else if (type == SQLOpType.ColumnAdd)
                        return Const.MSSQL.ColumnAdd;
                    break;
                case DatabaseType.MySQL:
                    if (type == SQLOpType.INSERT)
                        return Const.MySQL.INSERT;
                    else if (type == SQLOpType.DELETE)
                        return Const.MySQL.DELETE;
                    else if (type == SQLOpType.CREATE)
                        return Const.MySQL.CreateTable;
                    else if (type == SQLOpType.CREATE_PKDef)
                        return Const.MySQL.CreateTablePKDef;
                    else if (type == SQLOpType.CREATE_ColDef)
                        return Const.MySQL.CreateTableColumnDef;
                    else if (type == SQLOpType.ColumnAdd)
                        return Const.MySQL.ColumnAdd;
                    break;
                case DatabaseType.PostgreSQL:
                    if (type == SQLOpType.INSERT)
                        return Const.Postgres.INSERT;
                    else if (type == SQLOpType.DELETE)
                        return Const.Postgres.DELETE;
                    else if (type == SQLOpType.CREATE)
                        return Const.Postgres.CreateTable;
                    else if (type == SQLOpType.CREATE_PKDef)
                        return Const.Postgres.CreateTablePKDef;
                    else if (type == SQLOpType.CREATE_ColDef)
                        return Const.Postgres.CreateTableColumnDef;
                    else if (type == SQLOpType.ColumnAdd)
                        return Const.Postgres.ColumnAdd;
                    break;
                case DatabaseType.ES:
                case DatabaseType.HBase:
                case DatabaseType.TiDB:
                case DatabaseType.StarRocks:
                case DatabaseType.Druid:
                case DatabaseType.Oracle:
                default:
                    throw new Exception($"不支持的数据库参数类型:{DatabaseType}");
            }
            return null;
        }

        /// <summary>
        /// 通过数据库类型获取不同的SQL预制数据hashset集合
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        private HashSet<string> GetSQLSets(SQLSetType type)
        {
            switch (DatabaseType)
            {
                case DatabaseType.SQLServer:
                    if (type == SQLSetType.ValueType)
                        return Const.MSSQL.ValueType;
                    else if (type == SQLSetType.FunctionList)
                        return Const.MSSQL.FunctionList;
                    break;
                case DatabaseType.MySQL:
                    if (type == SQLSetType.ValueType)
                        return Const.MySQL.ValueType;
                    else if (type == SQLSetType.FunctionList)
                        return Const.MySQL.FunctionList;
                    break;
                case DatabaseType.PostgreSQL:
                    if (type == SQLSetType.ValueType)
                        return Const.MySQL.ValueType;
                    else if (type == SQLSetType.FunctionList)
                        return Const.MySQL.FunctionList;
                    break;
                case DatabaseType.ES:
                case DatabaseType.HBase:
                case DatabaseType.TiDB:
                case DatabaseType.StarRocks:
                case DatabaseType.Druid:
                case DatabaseType.Oracle:
                default:
                    throw new Exception($"不支持的数据库参数类型:{DatabaseType}");
            }
            return null;
        }
        #endregion

        #region Private Funcs
        protected string ProcessCaseString(string caseString)
        {
            if (!IsNeedLowerCase) return caseString;
            else return caseString?.ToLower();
        }
        #endregion

        #region 测试用

        /// <summary>
        /// 测试用
        /// </summary>
        /// <param name="entiy"></param>
        /// <returns></returns>
        internal List<SQLItem> BuildBusinessSQL(DataObject oldObj, DataObject newObj, bool isCreateTable = false)
        {
            if (newObj == null || newObj.Columns == null || newObj.Columns.Count == 0)
                throw new FasException(DataModelErrorCode.ParamError, "生成DataObject元数据SQL失败！检查检查参数");

            //生成Create Table语句， 如果本地存在DO 则额外生成column DLL语句
            List<SQLItem> resultSQL = new List<SQLItem>();
            if (isCreateTable)
                GetCreateTableSQL(newObj);
            if (oldObj != null)
            {
                resultSQL.AddRange(GetColumnDDL(oldObj, newObj));
            }
            return resultSQL;
        }
        #endregion

        #region PG特殊处理，后续用工厂搞
        private NpgsqlParameter GetPGParameters(DataType dataType, string columnName, object value)
        {
            NpgsqlDbType pgDataType;
            switch (dataType)
            {
                case DataType.Binary:
                    pgDataType = NpgsqlDbType.Bytea;
                    break;
                case DataType.Boolean:
                    pgDataType = NpgsqlDbType.Bit;
                    break;
                case DataType.Int:
                    pgDataType = NpgsqlDbType.Integer;
                    break;
                case DataType.Long:
                    pgDataType = NpgsqlDbType.Bigint;
                    break;
                case DataType.Decimal:
                    pgDataType = NpgsqlDbType.Numeric;
                    break;
                case DataType.DateTime:
                    pgDataType = NpgsqlDbType.Timestamp;
                    break;
                case DataType.Timestamp:
                    pgDataType = NpgsqlDbType.Timestamp;
                    break;
                case DataType.Date:
                    pgDataType = NpgsqlDbType.Date;
                    break;
                case DataType.Time:
                    pgDataType = NpgsqlDbType.Time;
                    break;
                case DataType.String:
                    pgDataType = NpgsqlDbType.Varchar;
                    break;
                case DataType.Record:
                case DataType.List:
                default:
                    throw new Exception($"不支持的PG数据类型:{dataType}");
            }
            var par = new NpgsqlParameter(columnName, pgDataType)
            {
                Value = value
            };
            return par;
        }
        #endregion
    }

    public enum SQLOpType
    {
        CREATE,
        CREATE_PKDef,
        CREATE_ColDef,
        INSERT,
        DELETE,
        ColumnAdd,
    }

    internal enum SQLSetType
    {
        FunctionList,
        ValueType
    }
}